library(RMySQL)
library(devtools)
source_url("https://gitee.com/ry2an/my_public_functions_r/raw/master/paste_vec.R")


insert_sql <- function(values = data.frame(), 
                   var_name = names(values), 
                   quote_mark = rep(T, length(values)),
                   tab_name = "sheet",
                   exec = T,
                   conn = dbConnect()){
  code <- paste("INSERT into ", tab_name, " (",paste_vec(var_name, sep = ", "),
                ") VALUES (",sep = "")
  for(i in 1:dim(values)[2]){
    if(quote_mark[i] == F | is.na(values[1,i])){
      if(is.na(values[1,i])){
        code <- paste(code, "NULL", sep = "")
      }else{
        code <- paste(code, values[1,i], sep = "")
      }
    }else{
      code <- paste(code,"\'", values[1,i], "\'",sep = "")
    }
    if(i != dim(values)[2]){
      code <- paste(code, ", ", sep = "")
    }
  }
  code <- paste(code, ")", sep = "")
  if(exec){
    dbExecute(conn = conn, statement = code)
  }
  return(code)
}


update_sql <- function(values = data.frame(), 
                   var_name = names(values), 
                   quote_mark = rep(T, length(values)),
                   tab_name = "sheet",
                   exec = T,
                   conn = dbConnect(),
                   idvar = var_name[1],
                   idpos = (1:length(var_name))[var_name == idvar]){
  code <- paste("UPDATE ", tab_name, " SET ",sep = "")
  for(i in 1:dim(values)[2]){
    if(quote_mark[i] == F | is.na(values[1,i])){
      if(is.na(values[1,i])){
        code <- paste(code, var_name[i]," = NULL", sep = "")
      }else{
        code <- paste(code, var_name[i], " = ", values[1,i], sep = "")
      }
    }else{
      code <- paste(code, var_name[i], " = ","\'", values[1,i], "\'",sep = "")
    }
    if(i != dim(values)[2]){
      code <- paste(code, ", ", sep = "")
    }
  }
  if(quote_mark[var_name == idvar]){
    code <- paste(code, " WHERE ",idvar," = \'", values[1,idpos],"\'", sep = "")
  }else{
    code <- paste(code, " WHERE ",idvar," = ", values[1,idpos], sep = "")
  }
  if(exec){
    dbExecute(conn = conn, statement = code)
  }
  return(code)
}

inset_or_update <- function(values = data.frame(), 
                                    var_name = names(values), 
                                    quote_mark = rep(T, length(values)),
                                    tab_name = "sheet",
                                    exec = T,
                                    conn = dbConnect(),
                                    idvar = var_name[1],
                                    idpos = (1:length(var_name))[var_name == idvar]){
  # test exist
  if(quote_mark[var_name == idvar]){
    temp_df <- dbGetQuery(conn = conn, 
                          statement = paste("select * from ",tab_name,
                                            " where ", idvar, " = \'", values[1,idpos],"\'",sep = ""))
  }else{
    temp_df <- dbGetQuery(conn = conn, 
                          statement = paste("select * from ",tab_name,
                                            " where ", idvar, " = ", values[1,idpos],sep = ""))
  }
  if(dim(temp_df)[1] > 0){
    res <- update_sql(values = values, var_name = var_name, quote_mark = quote_mark, 
               tab_name = tab_name, exec= exec,
               conn = conn, idvar = idvar, idpos = idpos)
  }else{
    res <- insert_sql(values = values, 
               var_name = var_name, 
               quote_mark = quote_mark,
               tab_name = tab_name,
               exec = exec,
               conn = conn)
  }
  return(res)
}


generate_mysql_table_sql <- function(excel_path) {
  # 加载openxlsx包
  if (!require(openxlsx)) install.packages("openxlsx")
  library(openxlsx)
  
  tryCatch({
    # 读取Excel第一张工作表
    df <- read.xlsx(excel_path, sheet = 1)
    
    # 校验数据列
    required_cols <- c("var_chn", "var_eng", "var_type")
    if (!all(required_cols %in% colnames(df))) {
      stop("Excel文件必须包含var_chn, var_eng, var_type三列")
    }
    
    # 类型映射字典
    type_mapping <- list(
      "varchar" = "VARCHAR(255)",
      "int" = "INT",
      "datetime" = "DATETIME",
      "numeric" = "DECIMAL(10,2)",
      "text" = "TEXT",
      "date" = "DATE"
    )
    
    # 构建字段定义
    columns <- c("`id` INT AUTO_INCREMENT PRIMARY KEY")
    for (i in 1:nrow(df)) {
      eng_name <- df$var_eng[i]
      chn_comment <- df$var_chn[i]
      data_type <- type_mapping[[df$var_type[i]]]
      
      if (is.null(data_type)) {
        stop(paste("第", i, "行的类型定义不合法，允许值：varchar/int/datetime/numeric/text/date"))
      }
      
      col_def <- sprintf("`%s` %s COMMENT '%s'", 
                         eng_name, data_type, chn_comment)
      columns <- c(columns, col_def)
    }
    
    # 生成完整SQL
    sql <- paste0(
      "CREATE TABLE table_name (\n  ",
      paste(columns, collapse = ",\n  "),
      "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    )
    
    return(sql)
    
  }, error = function(e) {
    message("错误：", e$message)
    return(NULL)
  })
}

#### test area ####
# 
# test_conn <- dbConnect(MySQL(), user='root', password='12qwaszx', dbname='test_db', host='localhost')
# 
# vec1 <- "aaa"
# vec2 <- 6
# vec3 <- NA
# 
# prep_data <- data.frame(vec1, vec2, vec3)
# values = prep_data
# var_name = c("var2", "id", "var3")
# quote_mark = c(T,F,T)
# 
# insert_sql(values = prep_data, var_name = var_name, 
#            quote_mark = quote_mark, tab_name = "test_tab1",
#            conn = test_conn)
# 
# 
# vec1 <- "bbb"
# vec2 <- 6
# vec3 <- NA
# 
# prep_data <- data.frame(vec1, vec2, vec3)
# values = prep_data
# var_name = c("var2", "id", "var3")
# quote_mark = c(T,F,T)
# cat(update_sql(values = prep_data, var_name = var_name, 
#            quote_mark = quote_mark, tab_name = "test_tab1",
#            conn = test_conn, idvar = "id"))
# 
# 
# 
# vec1 <- "bbb"
# vec2 <- 9
# vec3 <- NA
# prep_data <- data.frame(vec1, vec2, vec3)
# values = prep_data
# var_name = c("var2", "id", "var3")
# quote_mark = c(T,F,T)
# inset_or_update(values = prep_data, var_name = var_name, 
#            quote_mark = quote_mark, tab_name = "test_tab1",
#            conn = test_conn, idvar = "id")
